#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import connection
from django.db.models import Q
from django.db.models import Sum
from web.manager.log_manager import LogManager
from web.dao.base_dao import BaseDao
from web.constants.direction import Direction
from web.util.datetime_util import DatetimeUtil
from web.util.char_util import CharUtil
from web.constants.datetime_format import DatetimeFormat
from web.models.robot8_account import Robot8Account
from web.models.robot8_account_log import Robot8AccountLog
from web.models.robot8_commodity_future_transaction_record import Robot8CommodityFutureTransactionRecord
from web.models.commodity_future_date_contract_data import CommodityFutureDateContractData
from web.models.commodity_future_info import CommodityFutureInfo

Logger = LogManager.get_logger(__name__)

class Robot8AccountDao(BaseDao):
    """
    Robot8Account的dao类
    """

    model_class = Robot8Account

    def update_robot_account_before_sell_or_buy(self, current_transaction_date):
        """
        更新robot8_account表
        """

        Logger.info("更新robot8_account表")

        # 返回机器人账户
        order_by_list = ['id']
        robot8_account_list = self.find_list(dict(), dict(), order_by_list)

        for robot8_account in robot8_account_list:
            # 期货资产
            commodity_future_assets = float(0)

            # 计算每一个机器人账户，在某一天持有的期货的记录
            robot8_commodity_future_transaction_record_queryset = Robot8CommodityFutureTransactionRecord.objects.filter(
                Q(robot_name=robot8_account.robot_name)
                & ((Q(direction=1) & Q(sell_date__isnull=True) & Q(sell_price__isnull=True) & Q(
                    sell_lot__isnull=True))
                   | (Q(direction=-1) & Q(buy_date__isnull=True) & Q(buy_price__isnull=True) & Q(
                            buy_lot__isnull=True))))

            # 如果这个账号期货持仓为0，则查找下一个机器人
            if robot8_commodity_future_transaction_record_queryset is None or len(
                    robot8_commodity_future_transaction_record_queryset) == 0:
                Logger.info("账号[" + robot8_account.robot_name + "]期货持仓为0，查找下一个机器人")
                continue

            for robot8_commodity_future_transaction_record in robot8_commodity_future_transaction_record_queryset:
                # 期货信息
                commodity_future_info = CommodityFutureInfo.objects.get(
                    code=robot8_commodity_future_transaction_record.code)

                # 查找某个期货在某一天的收盘价
                # 期货的收盘价
                current_close_price = float(0)
                current_close_price_queryset = CommodityFutureDateContractData.objects.values('close_price').filter(
                    Q(code=robot8_commodity_future_transaction_record.code) & Q(
                        transaction_date=current_transaction_date))

                if len(current_close_price_queryset) == 0:
                    # 说明期货在这一天没有交易记录
                    Logger.info("期货[" + robot8_commodity_future_transaction_record.code + "]在日期[" +
                                str(current_transaction_date) + "]没有交易记录，开始查找前一个交易日的记录")

                    # 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
                    with connection.cursor() as cursor:
                        cursor.execute("select cfdd.close_price from (select * from c_f_date_contract_data t " +
                                       "where t.code = %s and t.transaction_date < to_date(%s, 'yyyy-mm-dd') "
                                       "order by t.transaction_date desc) cfdd where rownum <= 1",
                                       (robot8_commodity_future_transaction_record.code,
                                        DatetimeUtil.datetime_to_str(current_transaction_date, DatetimeFormat.Date_Format)))
                        row = cursor.fetchone()
                        current_close_price = row[0]
                else:
                    current_close_price = current_close_price_queryset[0]['close_price']

                # 期货资产
                if robot8_commodity_future_transaction_record.direction == Direction.Up:
                    commodity_future_assets = float(commodity_future_assets) + float(
                        robot8_commodity_future_transaction_record.buy_lot) * float(
                        robot8_commodity_future_transaction_record.buy_price) * float(
                        commodity_future_info.transaction_multiplier) * float(commodity_future_info.company_deposit) + (
                                                      float(current_close_price) - float(
                                                  robot8_commodity_future_transaction_record.buy_price)) * float(
                        robot8_commodity_future_transaction_record.buy_lot) * float(
                        commodity_future_info.transaction_multiplier)
                if robot8_commodity_future_transaction_record.direction == Direction.Down:
                    commodity_future_assets = float(commodity_future_assets) + float(
                        robot8_commodity_future_transaction_record.sell_lot) * float(
                        robot8_commodity_future_transaction_record.sell_price) * float(
                        commodity_future_info.transaction_multiplier) * float(commodity_future_info.company_deposit) + (
                                                      float(
                                                          robot8_commodity_future_transaction_record.sell_price) - float(
                                                  current_close_price)) * float(
                        robot8_commodity_future_transaction_record.sell_lot) * float(
                        commodity_future_info.transaction_multiplier)

            # 更新robot8_account表的commodityf_future_assets、total_assets字段
            if commodity_future_assets != 0:
                robot8_account.commodity_future_assets = commodity_future_assets
                robot8_account.total_assets = float(robot8_account.capital_assets) + float(commodity_future_assets)
                self.update(robot8_account)

    def update_robot_account_after_sell_or_buy(self, current_transaction_date):
        """
        根据当日期货平仓的收盘价，在期货平仓之后，更新robot8_account表
        """

        Logger.info("根据当日期货平仓的收盘价，在期货平仓之后，更新robot8_account表")

        # 返回机器人账户
        order_by_list = ['id']
        robot8_account_list = self.find_list(dict(), dict(), order_by_list)

        for robot8_account in robot8_account_list:
            # 持有的期货资产
            holding_commodity_future_assets = 0
            # 刚刚平仓的期货，变为了资金资产
            close_commodity_future_assets = 0
            # 期货持仓数量
            sell_or_buy_commodity_future_number = 0
            # 是否需要更新
            need_update = False

            # 计算每一个机器人账户，在某一天期货平仓后，持有的期货资产
            robot8_commodity_future_transaction_record_queryset = Robot8CommodityFutureTransactionRecord.objects.filter(
                Q(robot_name=robot8_account.robot_name)
                & ((Q(direction=1) & Q(sell_date__isnull=True) & Q(sell_price__isnull=True) & Q(
                    sell_lot__isnull=True))
                   | (Q(direction=-1) & Q(buy_date__isnull=True) & Q(buy_price__isnull=True) & Q(
                            buy_lot__isnull=True))))

            if robot8_commodity_future_transaction_record_queryset != None and len(robot8_commodity_future_transaction_record_queryset) !=0:
                need_update = True

                for robot8_commodity_future_transaction_record in robot8_commodity_future_transaction_record_queryset:
                    # 查询这个期货的信息
                    commodity_future_info = CommodityFutureInfo.objects.get(
                        code=CharUtil.extract_alpha(robot8_commodity_future_transaction_record.code))

                    # 查找某个期货在某一天的收盘价
                    # 期货的收盘价
                    current_close_price = float(0)
                    current_close_price_queryset = CommodityFutureDateContractData.objects.values('close_price').filter(
                        Q(code=robot8_commodity_future_transaction_record.code) & Q(
                            transaction_date=current_transaction_date))

                    if len(current_close_price_queryset) == 0:
                        # 说明期货在这一天没有交易记录
                        Logger.info("期货[" + robot8_commodity_future_transaction_record.code + "]在日期[" +
                                    str(current_transaction_date) + "]没有交易记录，开始查找前一个交易日的记录")

                        # 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
                        with connection.cursor() as cursor:
                            cursor.execute("select cfdd.close_price from (select * from c_f_date_contract_data t " +
                                           "where t.code = %s and t.transaction_date < to_date(%s, 'yyyy-mm-dd') "
                                           "order by t.transaction_date desc) cfdd where rownum <= 1",
                                           (robot8_commodity_future_transaction_record.code,
                                            DatetimeUtil.datetime_to_str(current_transaction_date, DatetimeFormat.Date_Format)))
                            row = cursor.fetchone()
                            current_close_price = row[0]
                    else:
                        current_close_price = current_close_price_queryset[0]['close_price']

                    # 期货资产
                    if robot8_commodity_future_transaction_record.direction == Direction.Up:
                        holding_commodity_future_assets = float(holding_commodity_future_assets) + float(
                            robot8_commodity_future_transaction_record.buy_price) * float(
                            robot8_commodity_future_transaction_record.buy_lot) * float(
                            commodity_future_info.transaction_multiplier) * float(commodity_future_info.company_deposit) + (
                                                                  float(current_close_price) - float(
                                                              robot8_commodity_future_transaction_record.buy_price)) * float(
                            robot8_commodity_future_transaction_record.buy_lot) * float(
                            commodity_future_info.transaction_multiplier)
                    if robot8_commodity_future_transaction_record.direction == Direction.Down:
                        holding_commodity_future_assets = float(holding_commodity_future_assets) + float(
                            robot8_commodity_future_transaction_record.sell_price) * float(
                            robot8_commodity_future_transaction_record.sell_lot) * float(
                            commodity_future_info.transaction_multiplier) * float(commodity_future_info.company_deposit) + (
                                                              float(
                                                                  robot8_commodity_future_transaction_record.sell_price - current_close_price)) * float(
                            robot8_commodity_future_transaction_record.sell_lot) * float(
                            commodity_future_info.transaction_multiplier)
                    sell_or_buy_commodity_future_number = sell_or_buy_commodity_future_number + 1

            # 计算每一个机器人账户，在某一天期货平仓后，刚刚平仓的期货，变为了资金资产
            robot8_commodity_future_transaction_record_queryset = Robot8CommodityFutureTransactionRecord.objects.filter(
                Q(robot_name=robot8_account.robot_name)
                & ((Q(direction=1) & Q(sell_date=current_transaction_date))
                   | ((Q(direction=-1) & Q(buy_date=current_transaction_date)))))

            if robot8_commodity_future_transaction_record_queryset != None and len(robot8_commodity_future_transaction_record_queryset) !=0:
                need_update = True
                for robot8_commodity_future_transaction_record in robot8_commodity_future_transaction_record_queryset:
                    # 查询这个期货的信息
                    commodity_future_info = CommodityFutureInfo.objects.get(
                        code=CharUtil.extract_alpha(robot8_commodity_future_transaction_record.code))

                    # 资金资产
                    if robot8_commodity_future_transaction_record.direction == Direction.Up:
                        close_commodity_future_assets = float(close_commodity_future_assets) + float(
                            robot8_commodity_future_transaction_record.buy_price) * float(
                            robot8_commodity_future_transaction_record.buy_lot) * float(
                            commodity_future_info.transaction_multiplier) * float(commodity_future_info.company_deposit) + (
                                                                float(
                                                                    robot8_commodity_future_transaction_record.sell_price) - float(
                                                            robot8_commodity_future_transaction_record.buy_price)) * float(
                            robot8_commodity_future_transaction_record.buy_lot) * float(
                            commodity_future_info.transaction_multiplier)
                    if robot8_commodity_future_transaction_record.direction == Direction.Down:
                        close_commodity_future_assets = float(close_commodity_future_assets) + float(
                            robot8_commodity_future_transaction_record.sell_price) * float(
                            robot8_commodity_future_transaction_record.sell_lot) * float(
                            commodity_future_info.transaction_multiplier) * float(commodity_future_info.company_deposit) + (
                                                                float(
                                                                    robot8_commodity_future_transaction_record.sell_price) - float(
                                                            robot8_commodity_future_transaction_record.buy_price)) * float(
                            robot8_commodity_future_transaction_record.sell_lot) * float(
                            commodity_future_info.transaction_multiplier)

            if need_update:
                # 当天平仓的期货的费用
                current_close_commission = \
                    Robot8CommodityFutureTransactionRecord.objects.values('close_commission').filter(
                        Q(robot_name=robot8_account.robot_name) & (
                                Q(direction=1) & Q(sell_date=current_transaction_date)) | (Q(direction=-1) & Q(
                            buy_date=current_transaction_date))).aggregate(
                        close_commission=Sum('close_commission'))['close_commission']
                if current_close_commission == None:
                    current_close_commission = 0

                # 查找某个账户的total_close_commission的总和
                total_close_commission = Robot8CommodityFutureTransactionRecord.objects.values('close_commission').filter(
                    robot_name=robot8_account.robot_name).aggregate(
                    close_commission=Sum('close_commission'))['close_commission']
                if total_close_commission == None:
                    total_close_commission = 0

                # 更新robot8_account表
                robot8_account.hold_commodity_future_number = sell_or_buy_commodity_future_number
                robot8_account.commodity_future_assets = holding_commodity_future_assets
                robot8_account.capital_assets = float(robot8_account.capital_assets) + float(
                    close_commodity_future_assets) - float(current_close_commission)
                robot8_account.total_assets = float(robot8_account.commodity_future_assets) + float(
                    robot8_account.capital_assets)
                robot8_account.total_close_commission = total_close_commission
                self.update(robot8_account)

    def update_robot_account_after_buy_or_sell(self, current_transaction_date):
        """
        更新robot8_account表的hold_commodity_future_number、commodity_future_assets、total_assets、capital_assets和total_open_commission字段
        """

        Logger.info(
            "更新robot8_account表的hold_commodity_future_number、commodity_future_assets、total_assets、capital_assets和total_open_commission字段")

        with connection.cursor() as cursor:

            # 返回机器人账户
            order_by_list = ['id']
            robot8_account_list = self.find_list(dict(), dict(), order_by_list)

            for robot8_account in robot8_account_list:
                # 持有的期货资产
                holding_commodity_future_assets = float(0)
                # 当天开仓的期货的资产，从资金资产变为了期货资产
                open_commodity_future_assets = float(0)
                # 期货开仓数量
                sell_or_buy_commodity_future_number = int(0)

                # 计算每一个机器人账户，在某一天的期货持仓记录
                robot8_commodity_future_transaction_record_queryset = Robot8CommodityFutureTransactionRecord.objects.filter(
                    Q(robot_name=robot8_account.robot_name)
                    & ((Q(direction=1) & Q(sell_date__isnull=True) & Q(sell_price__isnull=True) & Q(
                        sell_lot__isnull=True))
                       | (Q(direction=-1) & Q(buy_date__isnull=True) & Q(buy_price__isnull=True) & Q(
                                buy_lot__isnull=True))))

                # 如果没有期货开仓，则直接将robot8_account表中的记录插入到robot8_account_log表中
                if robot8_commodity_future_transaction_record_queryset == None or len(
                        robot8_commodity_future_transaction_record_queryset) == 0:
                    robot8_account_log = Robot8AccountLog()
                    robot8_account_log.date_ = current_transaction_date
                    robot8_account_log.robot_name = robot8_account.robot_name
                    robot8_account_log.hold_commodity_future_number = robot8_account.hold_commodity_future_number
                    robot8_account_log.commodity_future_assets = robot8_account.commodity_future_assets
                    robot8_account_log.capital_assets = robot8_account.capital_assets
                    robot8_account_log.total_assets = robot8_account.total_assets
                    robot8_account_log.total_open_commission = robot8_account.total_open_commission
                    robot8_account_log.total_close_commission = robot8_account.total_close_commission
                    robot8_account_log.save()
                    continue

                for robot8_commodity_future_transaction_record in robot8_commodity_future_transaction_record_queryset:
                    # 查询这个期货的信息
                    commodity_future_info = CommodityFutureInfo.objects.get(
                        code=CharUtil.extract_alpha(robot8_commodity_future_transaction_record.code))

                    # 查找某个期货在某一天的收盘价
                    # 期货的收盘价
                    current_close_price = float(0)
                    current_close_price_queryset = CommodityFutureDateContractData.objects.values('close_price').filter(
                        Q(code=robot8_commodity_future_transaction_record.code) & Q(
                            transaction_date=current_transaction_date))

                    if current_close_price_queryset == None or len(current_close_price_queryset) == 0:
                        # 说明期货在这一天没有交易记录
                        Logger.info("期货[" + robot8_commodity_future_transaction_record.code + "]在日期[" +
                                    DatetimeUtil.datetime_to_str(current_transaction_date) + "]没有交易记录，开始查找前一个交易日的记录")

                        # 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
                        cursor.execute("select cfdd.close_price from (select * from c_f_date_contract_data t " +
                                       "where t.code = %s and t.transaction_date < to_date(%s, 'yyyy-mm-dd') "
                                       "order by t.transaction_date desc) cfdd where rownum <= 1",
                                       (robot8_commodity_future_transaction_record.code,
                                        DatetimeUtil.datetime_to_str(current_transaction_date,
                                                                     DatetimeFormat.Date_Format_With_Line)))
                        row = cursor.fetchone()
                        current_close_price = row[0]
                    else:
                        current_close_price = current_close_price_queryset[0]['close_price']

                    # 某一天的持有的期货的资产
                    if robot8_commodity_future_transaction_record.direction == Direction.Up:
                        holding_commodity_future_assets = float(holding_commodity_future_assets) + float(
                            robot8_commodity_future_transaction_record.buy_lot) * float(
                            robot8_commodity_future_transaction_record.buy_price) * float(
                            commodity_future_info.transaction_multiplier) * float(
                            commodity_future_info.company_deposit) + (
                                                                  float(current_close_price) - float(
                                                              robot8_commodity_future_transaction_record.buy_price)) * float(
                            robot8_commodity_future_transaction_record.buy_lot) * float(
                            commodity_future_info.transaction_multiplier)
                    if robot8_commodity_future_transaction_record.direction == Direction.Down:
                        holding_commodity_future_assets = float(holding_commodity_future_assets) + float(
                            robot8_commodity_future_transaction_record.sell_lot) * float(
                            robot8_commodity_future_transaction_record.sell_price) * float(
                            commodity_future_info.transaction_multiplier) * float(
                            commodity_future_info.company_deposit) + (
                                                                  float(
                                                                      robot8_commodity_future_transaction_record.sell_price) - float(
                                                              current_close_price)) * float(
                            robot8_commodity_future_transaction_record.sell_lot) * float(
                            commodity_future_info.transaction_multiplier)
                    sell_or_buy_commodity_future_number = sell_or_buy_commodity_future_number + 1

                # 当天开仓的期货记录
                robot8_commodity_future_transaction_record_queryset = Robot8CommodityFutureTransactionRecord.objects.filter(
                    Q(robot_name=robot8_account.robot_name)
                    & ((Q(direction=1) & Q(buy_date=current_transaction_date))
                       | (Q(direction=-1) & Q(sell_date=current_transaction_date))))

                for robot8_commodity_future_transaction_record in robot8_commodity_future_transaction_record_queryset:
                    # 查询这个期货的信息
                    commodity_future_info = CommodityFutureInfo.objects.get(
                        code=CharUtil.extract_alpha(robot8_commodity_future_transaction_record.code))

                    # 当天开仓的期货的资产
                    if robot8_commodity_future_transaction_record.direction == Direction.Up:
                        open_commodity_future_assets = float(open_commodity_future_assets) + float(
                            robot8_commodity_future_transaction_record.buy_lot) * float(
                            robot8_commodity_future_transaction_record.buy_price) * float(
                            commodity_future_info.transaction_multiplier) * float(
                            commodity_future_info.company_deposit)
                    if robot8_commodity_future_transaction_record.direction == Direction.Down:
                        open_commodity_future_assets = float(open_commodity_future_assets) + float(
                            robot8_commodity_future_transaction_record.sell_lot) * float(
                            robot8_commodity_future_transaction_record.sell_price) * float(
                            commodity_future_info.transaction_multiplier) * float(
                            commodity_future_info.company_deposit)

                # 更新robot8_account表的hold_commodity_future_number、holding_commodity_future_assets、total_assets、capital_assets和total_open_commission字段
                # 当天开仓的期货的费用
                current_open_commission = \
                    Robot8CommodityFutureTransactionRecord.objects.values('open_commission').filter(
                        Q(robot_name=robot8_account.robot_name) & (
                                Q(direction=1) & Q(sell_date__isnull=True) & Q(sell_price__isnull=True) & Q(
                            sell_lot__isnull=True) & Q(buy_date=current_transaction_date)) | (Q(direction=-1) & Q(
                            buy_date__isnull=True) & Q(buy_price__isnull=True) & Q(buy_lot__isnull=True) & Q(
                            sell_date=current_transaction_date))).aggregate(
                        open_commission=Sum('open_commission'))['open_commission']
                if current_open_commission == None:
                    current_open_commission = 0
                # 总的开仓费用
                total_open_commission = Robot8CommodityFutureTransactionRecord.objects.values('open_commission').filter(
                    Q(robot_name=robot8_account.robot_name)).aggregate(open_commission=Sum('open_commission'))[
                    'open_commission']
                if total_open_commission == None:
                    total_open_commission = 0
                robot8_account.hold_commodity_future_number = sell_or_buy_commodity_future_number
                robot8_account.commodity_future_assets = holding_commodity_future_assets
                robot8_account.capital_assets = float(robot8_account.capital_assets) - float(
                    open_commodity_future_assets) - float(current_open_commission)
                robot8_account.total_assets = float(robot8_account.commodity_future_assets) + float(
                    robot8_account.capital_assets)
                robot8_account.total_open_commission = total_open_commission
                self.update(robot8_account)

                # 向表robot8_account_log中插入数据
                robot8_account_log = Robot8AccountLog()
                robot8_account_log.date_ = current_transaction_date
                robot8_account_log.robot_name = robot8_account.robot_name
                robot8_account_log.hold_commodity_future_number = robot8_account.hold_commodity_future_number
                robot8_account_log.commodity_future_assets = robot8_account.commodity_future_assets
                robot8_account_log.capital_assets = robot8_account.capital_assets
                robot8_account_log.total_assets = robot8_account.total_assets
                robot8_account_log.total_open_commission = robot8_account.total_open_commission
                robot8_account_log.total_close_commission = robot8_account.total_close_commission
                robot8_account_log.save()
